
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
#include <locale.h>

typedef void funcao();

struct NumberPerYear
{ 
	int  year;
	int number;
};

struct nDocentesPerInstPerYear
{ 
	char* inst;
	int nDocentes;
};

struct nDocentesPerDegreePerInstPerYear
{ 
    char* inst;
	char* degree;
	int nDocentes;
};

//##############################################################################
//Functions that help calculate the results - functions.c
//##############################################################################




//creates the name of the database
char* bdName(int ano)
{
	char bd[16] = "db/rebides0X";
	bd[11] = ano + '0';
	strcat(bd,".db");
	return bd;
}

//Function used by qsort to organize the type struct NumberPerYear
int comparenNumberPerYear(struct NumberPerYear *elem1, struct NumberPerYear *elem2)
{
   if ( elem1->number < elem2->number)
      return -1;

   else if (elem1->number > elem2->number)
      return 1;

   else
      return 0;
}

//Function used by qsort to organize type struct nDocentesPerInstPerYear
int compNDocentesPerInstPerYear(struct nDocentesPerInstPerYear *elem1, struct nDocentesPerInstPerYear *elem2)
{
	if ( elem1->nDocentes < elem2->nDocentes)
      return -1;

   else if (elem1->nDocentes > elem2->nDocentes)
      return 1;

   else
      return 0;
}

//Function used by qsort to organize type struct nDocentesPerDegreePerInstPerYear
int compNDocentesPerDegreePerInstPerYear(struct nDocentesPerDegreePerInstPerYear *elem1, struct nDocentesPerDegreePerInstPerYear *elem2)
{
	if ( elem1->nDocentes < elem2->nDocentes)
      return -1;

   else if (elem1->nDocentes > elem2->nDocentes)
      return 1;

   else
      return 0;
}

//Calllback put in the pointer notused the information from the query to the bd
int callbackInt(void **NotUsed, int argc, char **argv, char **azColName)
{
	*NotUsed = atoi( argv[0] ? argv[0] : "NULL");
	return 0;
}

//callback that fill the array with NDocentesPerInstPerYear structs 
//with the information from the query to the bd
int callBack_NDIY(struct nDocentesPerInstPerYear **NotUsed, int argc, char **argv, char **azColName)
{
	//allocates memory for the name of institution
	(*NotUsed)->inst = malloc((strlen(argv[0])+1)*sizeof(char));
	
	strcpy((*NotUsed)->inst,argv[0]);
	(*NotUsed)->nDocentes = atoi(argv[1]);

	//point to the next struct in the array
	(*NotUsed)++;

    return 0;
}

//callback that fill the array with nDocentesPerDegreePerInstPerYear structs 
//with the information from the query to the bd
//NotUsed(Its a Pointer to a Pointer for the first element of the array of strucks)
int callBack_NDDIY(struct nDocentesPerDegreePerInstPerYear **NotUsed, int argc, char **argv, char **azColName)
{
	//allocate memory for name of intitution and degree
	(*NotUsed)->inst = malloc((strlen(argv[0])+1)*sizeof(char));
	(*NotUsed)->degree = malloc((strlen(argv[1])+1)*sizeof(char));

	//copy information for the structure
	strcpy((*NotUsed)->inst,argv[0]);
	strcpy((*NotUsed)->degree,argv[1]);
	(*NotUsed)->nDocentes = atoi(argv[2]);

	//point to the next struct in the array
	(*NotUsed)++;

    return 0;
}


//Return a list with the number of instituitions of the years choosen by the user
//used by function nDocentesPerInstPerYear to know how many memory need to allocate
int* nInstYear()
{
	int i = 0;
	char bd[16] = "";
	int contagem;
	char* sql = "SELECT COUNT(DISTINCT estabelecimento) FROM fichas_docencia ORDER BY estabelecimento";
	struct NumberPerYear array[10];
	
	for(i = 0; i < 10; i++)
	{
		//Creates the path to the databases
		char bd[16] = "db/rebides0X";
		bd[11] = i + '0';
		strcat(bd,".db");

		//Open BD, do query and Close BD
		sqlite3 *db;
		char *zErrMsg = 0;
		int rc;
		rc = sqlite3_open(bd, &db);
		rc = sqlite3_exec(db, sql, callbackInt, &contagem, &zErrMsg);
		if( rc!=SQLITE_OK ){
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		}
		sqlite3_close(db);

		//Creates an array of structures to carry out the qsort per Nº of Institutions
		struct NumberPerYear temp;
		temp.year = i;
		temp.number = contagem;
		array[i] = temp;
	}
	
	return array;	
}

//Return a list with the number of degrees of the years choosen by the user
//used by function nDocentesPerDegreePerYear to know how many memory need to allocate
int* nDegreeYear()
{
	int i = 0;
	int contagem;
	char* sql = "SELECT COUNT(DISTINCT grau) FROM fichas_docencia ORDER BY grau";
	struct NumberPerYear array[10];
	
	for(i = 0; i < 10; i++)
	{
		//Creates the path to the databases
		char bd[16] = "db/rebides0X";
		bd[11] = i + '0';
		strcat(bd,".db");

		//Open BD, do query and Close BD
		sqlite3 *db;
		char *zErrMsg = 0;
		int rc;
		rc = sqlite3_open(bd, &db);
		rc = sqlite3_exec(db, sql, callbackInt, &contagem, &zErrMsg);
		if( rc!=SQLITE_OK ){
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		}
		sqlite3_close(db);

		//Creates an array of structures to carry out the qsort per Nº of Institutions
		struct NumberPerYear temp;
		temp.year = i;
		temp.number = contagem;
		array[i] = temp;
	}
	return array;
}

//Return a array with the sum of the number of degrees per institutions of the years choosen by the user
//used by the function nDocentesPerDegreePerInstPerYear to know how many memory need to allocate
int* nDegreeInstYear()
{
	int i = 0;
	int contagem;
	char* sql = "SELECT sum(totalGraus) FROM (select count(Distinct grau)\
					as totalGraus from fichas_docencia group by estabelecimento order by grau)";

	//int array[PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList)))];
	int* array = malloc(10*sizeof(int));
	
	for(i = 0; i < 10; i++)
	{
		//Creates the path to the databases
		char bd[16] = "db/rebides0X";
		bd[11] = i + '0';
		strcat(bd,".db");

		//Open BD, do query and Close BD
		sqlite3 *db;
		char *zErrMsg = 0;
		int rc;
		rc = sqlite3_open(bd, &db);
		rc = sqlite3_exec(db, sql, callbackInt, &contagem, &zErrMsg);
		if( rc!=SQLITE_OK ){
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		}
		sqlite3_close(db);

		//put data in the array
		array[i] = contagem;
	}
	
	return array;	
}


//##############################################################################
//End of functions.c
//##############################################################################


//Receive a PyObject that indicates whether to sort by year or by number of teachers
//returns a list with the number of teachers already ordered
void nDocentesPerYear(){

	int i = 0;
	int contagem;
	char* sql = "SELECT COUNT(*) FROM docentes";
	struct NumberPerYear array[10];
	
	for(i = 0; i <= 9; i++)
	{
		//Creates the path to the databases
		char bd[16] = "db/rebides0X";
		bd[11] = i + '0';
		strcat(bd,".db");

		//Open BD, do query and Close BD
		sqlite3 *db;
		char *zErrMsg = 0;
		int rc;
		rc = sqlite3_open(bd, &db);
		rc = sqlite3_exec(db, sql, callbackInt, &contagem, &zErrMsg);
		if( rc!=SQLITE_OK ){
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		}
		sqlite3_close(db);

		//Creates an array of structures to carry out the qsort per year or per nº Teachers
		struct NumberPerYear temp;
		temp.year = i;
		temp.number = contagem;
		array[i] = temp;
	}
}

//Show the number of teacher per Institution per year
//Create a File with this information
//Returns a list of tuples with the information
void nDocentesPerInstPerYear()
{
	int i = 0;
	int j = 0;
	int nInst = 0;
	char* sql = "SELECT DISTINCT estabelecimento, COUNT(docente) FROM fichas_docencia GROUP BY estabelecimento";

	struct nDocentesPerInstPerYear* nDIY;
	struct nDocentesPerInstPerYear* free_nDIY;

	for(i = 0; i < 10; i++)
	{

		//Creates the path to the databases
		char bd[16] = "db/rebides0X";
		bd[11] = i + '0';
		strcat(bd,".db");
		

		nDIY = malloc(500*sizeof(struct nDocentesPerInstPerYear));

		free_nDIY = nDIY;

		//Open BD, do query and Close BD
		sqlite3 *db;
		char *zErrMsg = 0;
		int rc;
		rc = sqlite3_open(bd, &db);
		//rc = sqlite3_exec(db, sql, callBack_NDIY, &nDIY, &zErrMsg);
		if( rc!=SQLITE_OK ){
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		}
		sqlite3_close(db);

		//arrange the items in the array
		qsort(free_nDIY, 500, sizeof(struct nDocentesPerInstPerYear),compNDocentesPerInstPerYear);		

		//Insert Instituitions and the number of teachers in list of tuples
		//free memory from struct
		for(j = 0; j < 500; j++)
		{
			free(free_nDIY[j].inst);
		}
		free(free_nDIY);
	}		

}

//Show the number of teacher per Degree per year
//Create a File with this information
//Returns a list of tuples with the information
void nDocentesPerDegreePerYear()
{
	int i = 0;
	int j = 0;
	char* sql = "SELECT DISTINCT grau, COUNT(docente) FROM fichas_docencia GROUP BY grau";

	struct nDocentesPerInstPerYear* nDIY;
	struct nDocentesPerInstPerYear* free_nDIY;

	for(i = 0; i < 10; i++)
	{
		//Creates the path to the databases
		char bd[16] = "db/rebides0X";
		bd[11] = i + '0';
		strcat(bd,".db");

		nDIY = malloc(500*sizeof(struct nDocentesPerInstPerYear));

		//save the reference of the first element of the array to use later
		//its necessary to save because the callback function moves the pointer
		//to the end of the array
		free_nDIY = nDIY;

		//Open BD, do query and Close BD
		sqlite3 *db;
		char *zErrMsg = 0;
		int rc;
		rc = sqlite3_open(bd, &db);
		rc = sqlite3_exec(db, sql, callbackInt, &nDIY, &zErrMsg);
		if( rc!=SQLITE_OK ){
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		}
		sqlite3_close(db);

		//arrange the items in the array
		qsort(free_nDIY, 500, sizeof(struct nDocentesPerInstPerYear),compNDocentesPerInstPerYear);		

		//Insert degree and the number of teachers in list of tuples
		//free memory from struct
		for(j = 0; j < 500; j++)
		{
			free(free_nDIY[j].inst);
		}
		free(free_nDIY);
	}		
}

//Information about teachers hiring
//return a PyList with tuples(year, hiring) with the information
void teacherHiring()
{
	int i = 0;
	int j = 0;
	int contagem = 0;
	int contTemp = 0;

	//create the sql query
	char* sql = malloc(200*sizeof(char));
	strcpy(sql, "SELECT count(docente) FROM fichas_docencia WHERE estabelecimento='Academia Militar'");
	
	for(i = 0; i < 10; i++)
	{
		//Creates the path to the databases
		char bd[16] = "db/rebides0X";
		bd[11] = i + '0';
		strcat(bd,".db");
		

		//Open BD, do query and Close BD
		sqlite3 *db;
		char *zErrMsg = 0;
		int rc;
		rc = sqlite3_open(bd, &db);
		rc = sqlite3_exec(db, sql, callbackInt, &contagem, &zErrMsg);
		if( rc!=SQLITE_OK ){
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		}
		sqlite3_close(db);

		//save number o teacher in a temporary variable to compare next year
		contTemp = contagem;
	}	
}


int main()
{
	int i;
	//for(i = 0; i < 100; i++)
	//{
	nDocentesPerInstPerYear();
	nDocentesPerDegreePerYear();
	nDegreeYear();
	nInstYear();
	
	nDegreeInstYear();
	teacherHiring();
	nDocentesPerYear();
	//}
}







